'''
Parse html files downloaded from LFC History to Liverbird database
'''

if __name__ == '__main__':
    pass

class Game_Fixer():

    def __init__(self):
        pass

    def process(self):

        id_dict = {}
        
        id_lines = open("games2", 'r').readlines()
        
        for line in id_lines:
            s= line.strip().split('\t')
        
            ''' skip empty links '''
            if s[1] == "\\N":
                #print ("nf",  s[0],  s)
                continue
            
            ''' find duplicate linls '''
            if id_dict.get(s[1]):
                print ("already got", s[1])
            id_dict[s[1]] = s[0]
        
        
        input = open("pl_list_x2.txt", 'r').readlines()
        output = open("pl_list_may26", 'w')
        
        for line in input:
            s = line.split('(')
            new_line = ""
            
            for item in s:
                if not item[0].isdigit():
                    new_line += item
                    continue
                    
                #print (item)
                
                h_id = item.split(',')[0]
        
                try:
                    l_id = id_dict[h_id]
                except KeyError:
                    print ("not found key",  h_id)
                    continue
                
                '''match id, player teamsheet number, shirt number, player id, came
                on, came off, yellow card, second yellow, red card '''
                itemsplit = item.split(',')

                c = 0
                for i in itemsplit:
                    if len(i) == 0:
                        itemsplit[c] = "NULL"
                    c+=1
                
                snum = itemsplit[1]
                fnum = itemsplit[2]
                pid  = itemsplit[3]
                on   = itemsplit[4]
                
                if len(itemsplit[5]) == 0:
                    off = "NULL"
                else:
                    off  = itemsplit[5]
                    
                cap  = itemsplit[6]
                yc   = itemsplit[7]
                sy   = itemsplit[8]
                rc   = itemsplit[9]
                
                new_line += " ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}"\
                .format(l_id, pid, snum, fnum, on, off, cap, yc, sy, rc)
                
                if rc.find(';') < 0:
                    new_line += ','

            #print (new_line)
            #break
            output.write(new_line)



    def create_player_dict(self):
        
        ''' create games ID dict'''
        id_lines = open("games2", 'r').readlines()
        id_dict = {}
        for line in id_lines:
            s= line.strip().split('\t')
            id_dict[s[1]] = s[0]     

        ''' parse history file '''
        self.his_dict = {}
        self.dump = open("full_dump.txt", 'r').readlines()
    
        for line in self.dump:
            game_id = line.split('LFC_id\',')[1].split(']')[0]
            
            
            pl_list = line.split('Players\',')[1]
            sn = 0
            for player in pl_list.split('], ['):
                if '[]' in player:
                    continue
        
                sn += 1 
                pl_id = player.split(', ')[2]
                hash = int(game_id)*100 + int(sn)
                self.his_dict[hash] = pl_id
        
                
        
        ''' create player pairs '''
        self.pairs = {}
        input = open("pl_list_x2.txt", 'r').readlines()
        for line in input:
            s = line.split('(')
            for item in s:
                if not item[0].isdigit():
                    continue          
                
                game_id = item.split(',')[0]
                pl_sn = item.split(',')[1]
                bird_pl_id = item.split(',')[3]

                
                his_pl_id = self.his_dict[int(game_id)*100 + int(pl_sn)]
                #print (game_id, pl_sn, bird_pl_id, int(game_id)*100 + int(pl_sn))
                #print (bird_pl_id, " - ", his_pl_id)
                
                self.pairs[bird_pl_id] = his_pl_id

        #print (self.pairs)
        
        ''' create command '''
        output = open("players_match_may27.txt", 'w')
        for key in self.pairs.keys():
            output.write("update content_type_player_profile set field_lfch_player_value=\'{0}\' where nid=\'{1}\';\n".format(self.pairs[key], key))

    def fix_duplicate_players(self, id):
        game_list = open('data/pl_list_may26', 'r', encoding = 'utf-8').readlines()
        count = 0
        for line in game_list:
            for i in line.split('('):
                loc = i.find('\'06'.format(id))#                loc = i.find('\'{0}\''.format(id))
                if loc > 0:
                    count += 1
                    new_id =  (i[loc-8:loc-3].replace('\'',''))
                    x = int(i[loc+1:loc+5])
                    x1 = x+10000
                    print ('update player_stats set player_stats.player=\'{1}\' where player_stats.match=\'{0}\' and player_stats.player=\'{2}\';'\
                           .format(new_id, x1, x))
        
        print(count)

    def find_games_no_stat(self):
        all_games = open('data/games_dump', 'r').readlines()
        stat_games = open('data/games_with_stats_dump', 'r').readlines()
        
        no_stat_list = []
        stat_list = []
        
        for line in stat_games:
            stat_list.append(line.strip())
        
        for line in all_games:
            items = line.strip().split('\t')
            bird_id = items[0]
            timestamp = items[2]
            
            if not bird_id in stat_list:
                if items[1] == '\\N':
                    hist_id = ''
                    #print ('No lfchistory id for game', bird_id, timestamp)
                    continue
                else:
                    hist_id = int(items[1])
                
                no_stat_list.append([bird_id, hist_id, timestamp[:-9]])
        
        print (len(no_stat_list), no_stat_list)
        
        dump = open('data/official_dump.txt', 'r', encoding = 'utf-8').readlines()
        
        f = 0
        nf = 0
        fr = 0
        
        for game in no_stat_list:
            if game[1] > 9000:
                fr += 1
                continue
            
            for line in dump:
                line_id =  line.split(',',1)[1][1:5]
                game_id = str(game[1])

                if line_id.find(game_id) >= 0:
                    f += 1 #print ('found', game, line_id, game_id)
                    break
            else:
                nf += 1
                print ('not found game dated at', game[2] )        
        
        
        print (f, nf, fr, len(no_stat_list))
        
        return all_list
        
#gf = Game_Fixer()
#gf.process()

#gf.create_player_dict()

#gf.find_games_no_stat()



insert_command = 'INSERT INTO term_data (vid, name, description) VALUES (6, \'{0}\', \'{1}\');'

modify_command = 'UPDATE  content_type_game SET field_referee_value = \'{0}\' WHERE  field_lfch_game_value = \'{1}\';'

full_list = []

def read_fix(file):
    with open(file, 'r', encoding = 'utf-8') as infile:
         for line in infile.readlines():
                (code, name, ids) = line.split('\t', 2)
                ids = ids.split('\t')
                full_list.append([code, ids])

def modify_gamesinsert_refs():
    for item in full_list:
        print (insert_command.format(item[0], ''))

def modify_games():
    for item in full_list:
        for game in item[1]:
            print (modify_command.format(item[0], game))

read_fix('fix_refs2.txt')
modify_games()


print ("Done")

outfile = open('data/339_ref_trans.txt', 'w', encoding = 'utf-8')

ref_names = []
for line in open('data/term_data.txt', 'r', encoding = 'utf-8').readlines():
    (id, type, name, descr, rest) = line.strip().split('\t', 4)
    if type.strip() == '6':
        ref_names.append([name, descr, id])


ref_exc = {}
for line in open('data/ref_exception.txt', 'r', encoding = 'utf-8').readlines():
    (id, name) = line.strip().split('\t', 1)
    ref_exc[name] = id

import re
for line in open('data/ref_fix_names.txt', 'r', encoding = 'utf-8').readlines():

    l = re.search(' [А-Я]', line)
    eng_name = line[:l.start()] 
    rus_name = line[l.start()+1:-1]
    #print (rus_name, '-' , eng_name)
    #command = 'UPDATE term_data SET name = \'{0}\' WHERE name = \'{1}\';'.format(rus_name, eng_name)

    if rus_name in ref_exc:
        command = 'UPDATE term_data SET name = \'{0}\' WHERE tid = \'{1}\';\n'.format(rus_name, ref_exc[rus_name])
        outfile.write (command)
        continue
        
            
    for name in ref_names:
        if eng_name == name[0] or eng_name == name[1]:
            command = 'UPDATE term_data SET name = \'{0}\' WHERE tid = \'{1}\';\n'.format(rus_name, name[2])
            outfile.write (command)
            break
    else:
        print ('not found', eng_name, rus_name)

outfile.close()

